package com.hnkywl.sts.dao.tjbb;

import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: SDE09
 * Date: 13-9-28
 * Time: 下午5:14
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class FkbbDao extends SimpleHibernateTemplate {

    public List<Map> findfkbbList(Date enddt) {

        String sql = "select ck_mc,ck_id,kh_id,kh_qc,wzlb_id,wzlb_name,gg_id,gg_name,cd_id,cd_z,gg_cz,gg_ggxh,\n" +
                "sum(ky_js) ky_js,sum(ky_sl) ky_sl,sum(ht_js) ht_js,sum(ht_sl) ht_sl,sum(wck_js) wck_js, sum(wck_sl) wck_sl,\n" +
                "sum(kgd_js) kgd_js,sum(kgd_sl) kgd_sl,sum(gd_js) gd_js,sum(gd_sl) gd_sl from (\n" +
                "select ck.mc as ck_mc,\n" +
                "       ck.id as ck_id,\n" +
                "       gsjg.id as kh_id,\n" +
                "       gsjg.qc as kh_qc,\n" +
                "       rjwzbm.id as wzlb_id,\n" +
                "       rjwzbm.name as wzlb_name,\n" +
                "       gg.id as gg_id,\n" +
                "       gg.name as gg_name,\n" +
                "       cd.id as cd_id,\n" +
                "       cd.z cd_z,\n" +
                "       gg.cz as gg_cz,\n" +
                "       gg.ggxh as gg_ggxh,\n" +
                "       kh.js as ky_js,\n" +
                "       kh.sl as ky_sl,\n" +
                "       NVL((select sum(xsmx1.js)\n" +
                "             from t_biz_xsmx xsmx1\n" +
                "             left join t_biz_xsd x1\n" +
                "               on xsmx1.xsd_id = x1.id\n" +
                "            where x1.kh_id = gsjg.id\n" +
                "              and xsd.deleted != 1),\n" +
                "           0) as ht_js,\n" +
                "       NVL((select sum(xsmx1.sbsl)\n" +
                "             from t_biz_xsmx xsmx1\n" +
                "             left join t_biz_xsd x1\n" +
                "               on xsmx1.xsd_id = x1.id\n" +
                "            where x1.kh_id = gsjg.id\n" +
                "              and xsd.deleted != 1),\n" +
                "           0) as ht_sl,\n" +
                "       NVL((select sum(nvl(xsmx1.js, 0) - nvl(xsmx1.ck_js, 0))\n" +
                "             from t_biz_xsmx xsmx1\n" +
                "             left join t_biz_xsd x1\n" +
                "               on xsmx1.xsd_id = x1.id\n" +
                "            where x1.kh_id = gsjg.id\n" +
                "              and xsd.deleted != 1),\n" +
                "           0) as wck_js,\n" +
                "       NVL((select sum(nvl(xsmx1.sbsl, 0) - nvl(xsmx1.ck_sbsl, 0))\n" +
                "             from t_biz_xsmx xsmx1\n" +
                "             left join t_biz_xsd x1\n" +
                "               on xsmx1.xsd_id = x1.id\n" +
                "            where x1.kh_id = gsjg.id\n" +
                "              and xsd.deleted != 1),\n" +
                "           0) as wck_sl,\n" +
                "       kh.js as kgd_js,\n" +
                "       kh.kkdsl as kgd_sl,\n" +
                "       kh.gdjshj as gd_js,\n" +
                "       kh.gdslhj as gd_sl\n" +
                "  from t_biz_kh kh\n" +
                "  left join t_sys_gg gg\n" +
                "    on kh.jtwz_id = gg.id\n" +
                "  left join T_SYS_SJZDZ cd\n" +
                "    on kh.cd_sjzdz_id = cd.id\n" +
                "  left join t_sys_pm pm\n" +
                "    on gg.pm_id = pm.id\n" +
                "  left join t_sys_rjwzbm rjwzbm\n" +
                "    on pm.rjwzbm_id = rjwzbm.id\n" +
                "  left join t_sys_ck ck\n" +
                "    on kh.ck_id = ck.id\n" +
                "  left join t_biz_xsmx xsmx\n" +
                "    on xsmx.kh_id = kh.id\n" +
                "  left join t_biz_xsd xsd\n" +
                "    on xsmx.xsd_id = xsd.id\n" +
                "  left join t_sys_gsjg gsjg\n" +
                "    on kh.gys_id = gsjg.id\n" +
                "    ) group by ck_mc,ck_id,kh_id,kh_qc,wzlb_id,wzlb_name,gg_id,gg_name,cd_id,cd_z,gg_cz,gg_ggxh" ;

        SQLQuery sqlQuery = createSQLQuery(sql);
        List<Map> result = sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
        return result;
    }
}
